﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using Vertica.Data.VerticaClient;

namespace NHibernateVertica
{
    public class VerticaDataHelper
    {
        private static readonly string ConnectionString = ConfigurationManager.ConnectionStrings["Vertica7"].ConnectionString;

        /// <summary>
        /// 执行Sql返回DataTable
        /// </summary>
        /// <param name="sqlQuery">查询语句</param>
        /// <returns>DataTable</returns>
        public DataTable GetDataTable(string sqlQuery)
        {
            VerticaCommand command = GetSqlStringCommand(sqlQuery);
            return ExecuteDataTable(command);
        }

        /// <summary>
        /// 执行SQL返回int
        /// </summary>
        /// <param name="ex_sql">(增、删、改)语句</param>
        /// <returns></returns>
        public int GetQueryCount(string ex_sql)
        {
            VerticaCommand command = GetSqlStringCommand(ex_sql);
            return int.Parse(ExecuteScalar(command).ToString());
        }

        /// <summary>
        /// 执行SQL返回int
        /// </summary>
        /// <param name="ex_sql">(增、删、改)语句</param>
        /// <returns></returns>
        public int GetNonQuery(string ex_sql)
        {
            VerticaCommand command = GetSqlStringCommand(ex_sql);
            return ExecuteNonQuery(command);
        }

        /// <summary>
        /// 执行Sql语句
        /// </summary>
        /// <param name="sqlQuery">Sql语句</param>
        /// <returns>返回VerticaCommand对象</returns>
        public VerticaCommand GetSqlStringCommand(string sqlQuery)
        {
            var cmd = new VerticaCommand(sqlQuery) { CommandType = CommandType.Text };
            return cmd;
        }



        #region 增加参数
        /// <summary>
        /// 增加参数
        /// </summary>
        /// <param name="cmd">cmd对象</param>
        /// <param name="parametersCollection">VerticaParameterCollection数据集合</param>
        public void AddParameterCollection(VerticaCommand cmd, VerticaParameterCollection parametersCollection)
        {
            foreach (VerticaParameter parameters in parametersCollection)
            {
                cmd.Parameters.Add(parameters);
            }
        }

        /// <summary>
        /// 增加参数
        /// </summary>
        /// <param name="cmd">cmd对象</param>
        /// <param name="parameters"></param>
        public void AddParameters(VerticaCommand cmd, VerticaParameter[] parameters)
        {
            if (parameters.Count() != 0)
            {
                cmd.Parameters.AddRange(parameters);
            }
        }
        /// <summary>
        /// 增加参数
        /// </summary>
        /// <param name="cmd">cmd对象</param>
        /// <param name="parameterName">parameterName</param>
        /// <param name="sqlType">类型</param>
        /// <param name="size">大小</param>
        public void AddInParameter(VerticaCommand cmd, string parameterName, DbType sqlType, int size)
        {
            var parameters = cmd.CreateParameter();
            parameters.DbType = sqlType;
            parameters.ParameterName = parameterName;
            parameters.Size = size;
            parameters.Direction = ParameterDirection.Input;
            cmd.Parameters.Add(parameters);
        }

        public void AddInParameter(VerticaCommand cmd, string parameterName, DbType sqlType, int size, object value)
        {
            var parameters = cmd.CreateParameter();
            parameters.ParameterName = parameterName;
            parameters.DbType = sqlType;
            parameters.Value = value;
            parameters.Size = size;
            parameters.Direction = ParameterDirection.Input;
            cmd.Parameters.Add(parameters);
        }
        public void AddInParameter(VerticaCommand cmd, string parameterName, DbType sqlType)
        {
            var parameters = cmd.CreateParameter();
            parameters.ParameterName = parameterName;
            parameters.DbType = sqlType;
            parameters.Direction = ParameterDirection.Input;
            cmd.Parameters.Add(parameters);
        }
        public VerticaParameter GetParameter(VerticaCommand cmd, string parameterName)
        {
            return cmd.Parameters[parameterName];
        }

        #endregion

        #region 执行
        /// <summary>
        /// 执行VerticaCommand命令，返回DataSet对象
        /// </summary>
        /// <param name="cmd">VerticaCommand对象</param>
        /// <returns>返回DataSet对象</returns>
        public DataSet ExecuteDataSet(VerticaCommand cmd)
        {
            using (var conn = new VerticaConnection(ConnectionString))
            {
                cmd.Connection = conn;
                conn.Open();
                var da = new VerticaDataAdapter(cmd);
                var ds = new DataSet();
                da.Fill(ds);
                da.Dispose();
                return ds;
            }
        }

        /// <summary>
        /// 执行VerticaCommand命令，返回DataTable对象
        /// </summary>
        /// <param name="cmd">VerticaCommand命令</param>
        /// <returns>返回DataTable对象</returns>
        public DataTable ExecuteDataTable(VerticaCommand cmd)
        {
            using (var conn = new VerticaConnection(ConnectionString))
            {
                cmd.Connection = conn;
                conn.Open();
                var da = new VerticaDataAdapter(cmd);
                var dt = new DataTable();
                da.Fill(dt);
                da.Dispose();
                return dt;
            }
        }

        /// <summary>
        /// 执行VerticaCommand命令，返回DataTable对象
        /// </summary>
        /// <param name="cmd">VerticaCommand命令</param>
        /// <returns>返回DataTable对象</returns>
        public DataTable ExecuteDataTable(VerticaCommand cmd, string Connection)
        {
            using (var conn = new VerticaConnection(Connection))
            {
                cmd.Connection = conn;
                conn.Open();
                var da = new VerticaDataAdapter(cmd);
                var dt = new DataTable();
                da.Fill(dt);
                da.Dispose();
                return dt;
            }
        }

        /// <summary>
        /// 执行VerticaCommand命令，返回SqlDataReader对象
        /// </summary>
        /// <param name="cmd">VerticaCommand命令</param>
        /// <returns>返回SqlDataReader对象</returns>
        public VerticaDataReader ExecuteReader(VerticaCommand cmd)
        {
            using (var conn = new VerticaConnection(ConnectionString))
            {
                cmd.Connection = conn;
                conn.Open();
                var reader = cmd.ExecuteReader();
                return reader;
            }
        }

        /// <summary>
        /// 执行VerticaCommand命令，返回Int
        ///</summary>
        /// <param name="cmd">VerticaCommand命令</param>
        /// <returns>返回Int</returns>
        public int ExecuteNonQuery(VerticaCommand cmd)
        {
            using (var conn = new VerticaConnection(ConnectionString))
            {
                cmd.Connection = conn;
                conn.Open();
                var ret = cmd.ExecuteNonQuery();
                return ret;
            }
        }

        /// <summary>
        /// 执行VerticaCommand命令，返回Object
        /// </summary>
        /// <param name="cmd">VerticaCommand命令</param>
        /// <returns>返回Object</returns>
        public object ExecuteScalar(VerticaCommand cmd)
        {
            using (var conn = new VerticaConnection(ConnectionString))
            {
                cmd.Connection = conn;
                conn.Open();
                var ret = cmd.ExecuteScalar();
                return ret;
            }
        }
        #endregion

        #region 批量插入数据
        /// <summary>
        /// 批量插入数据到vertica数据库
        /// </summary>
        /// <param name="dt">数据源</param>
        /// <param name="strTableName">插入的目标表名</param>
        public void BulkCopy(DataTable dt, string strTableName)
        {
            if (dt == null || dt.Rows.Count == 0)
            {
                return;
            }
            if (dt.Columns.Count == 0)
            {
                throw new Exception("无数据");
            }
            //从datatable中获取列名
            var lstField = new List<string>();
            for (var colIndex = 0; colIndex < dt.Columns.Count; colIndex++)
            {
                lstField.Add(dt.Columns[colIndex].ColumnName);
            }
            string strFiledList = $"({string.Join(",", lstField.ToArray())})";
            //拼写copy语句
            const char rowSplit = '\n';
            const char colSplit = '\t';

            string strCopyStatement =
                $"copy {strTableName}{strFiledList} from stdin record terminator E'{rowSplit}' delimiter E'{colSplit}' enforcelength no commit";
            //按照copy语句中的分隔符，分隔数据源
            var sbText = new StringBuilder();
            foreach (DataRow dr in dt.Rows)
            {
                var bFirstField = true;
                for (var colIndex = 0; colIndex < dt.Columns.Count; colIndex++)
                {
                    var strVal = GetDataString(dr, colIndex);
                    if (bFirstField)
                    {
                        sbText.Append(strVal);
                        bFirstField = false;
                    }
                    else
                    {
                        sbText.AppendFormat("{0}{1}", colSplit, strVal);
                    }
                }
                sbText.Append(rowSplit);
            }
            var strTemp = sbText.ToString();
            var buff = Encoding.UTF8.GetBytes(strTemp);
            using (var ms = new MemoryStream())
            {
                ms.Write(buff, 0, buff.Length);
                ms.Flush();
                ms.Position = 0;
                using (var conn = new VerticaConnection(ConnectionString))
                {
                    conn.Open();
                    var txn = conn.BeginTransaction();
                    try
                    {
                        var vcs = new VerticaCopyStream(conn, strCopyStatement);
                        //批量插入数据
                        vcs.Start();
                        vcs.AddStream(ms, false);
                        vcs.Execute();
                        vcs.Finish();
                        var lstRejected = vcs.Rejects;
                        if (lstRejected.Count > 0)
                        {
                            txn.Rollback();
                            throw new Exception($"有{lstRejected.Count}条数据有误");
                        }
                        txn.Commit();
                    }
                    catch (Exception ex)
                    {
                        txn.Rollback();
                        throw (ex);
                    }
                    finally
                    {
                        ms.Close();
                    }
                }
            }
        }

        /// <summary>
        /// 批量插入数据到vertica数据库
        /// </summary>
        /// <param name="dt">数据源</param>
        /// <param name="strTableName">插入的目标表名</param>
        public void BulkCopy(VerticaConnection conn, DataTable dt, string strTableName)
        {
            if (dt == null || dt.Rows.Count == 0)
            {
                return;
            }
            if (dt.Columns.Count == 0)
            {
                throw new Exception("无数据");
            }
            //从datatable中获取列名
            var lstField = new List<string>();
            for (var colIndex = 0; colIndex < dt.Columns.Count; colIndex++)
            {
                lstField.Add(dt.Columns[colIndex].ColumnName);
            }
            string strFiledList = $"({string.Join(",", lstField.ToArray())})";
            //拼写copy语句
            const char rowSplit = '\n';
            const char colSplit = '\t';

            string strCopyStatement =
                $"copy {strTableName}{strFiledList} from stdin record terminator E'{rowSplit}' delimiter E'{colSplit}' enforcelength no commit";
            //按照copy语句中的分隔符，分隔数据源
            var sbText = new StringBuilder();
            foreach (DataRow dr in dt.Rows)
            {
                var bFirstField = true;
                for (var colIndex = 0; colIndex < dt.Columns.Count; colIndex++)
                {
                    var strVal = GetDataString(dr, colIndex);
                    if (bFirstField)
                    {
                        sbText.Append(strVal);
                        bFirstField = false;
                    }
                    else
                    {
                        sbText.AppendFormat("{0}{1}", colSplit, strVal);
                    }
                }
                sbText.Append(rowSplit);
            }
            var strTemp = sbText.ToString();
            var buff = Encoding.UTF8.GetBytes(strTemp);
            using (var ms = new MemoryStream())
            {
                ms.Write(buff, 0, buff.Length);
                ms.Flush();
                ms.Position = 0;
                try
                {
                    var vcs = new VerticaCopyStream(conn, strCopyStatement);
                    //批量插入数据
                    vcs.Start();
                    vcs.AddStream(ms, false);
                    vcs.Execute();
                    vcs.Finish();
                    var lstRejected = vcs.Rejects;
                    if (lstRejected.Count > 0)
                    {
                        throw new Exception($"有{lstRejected.Count}条数据有误");
                    }
                }
                catch (Exception ex)
                {
                    throw (ex);
                }
                finally
                {
                    ms.Close();
                }
            }
        }

        private static string GetDataString(DataRow dr, int colIndex)
        {
            var strVal = "";
            if (!dr.IsNull(colIndex))
            {
                strVal = dr[colIndex].ToString();
            }
            return strVal;
        }

        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="sqlStringList">多条SQL语句</param>		
        public static int ExecuteSqlTran(List<string> sqlStringList)
        {
            using (var sqlconn = new VerticaConnection(ConnectionString))
            {
                var cmd = new VerticaCommand { Connection = sqlconn };
                sqlconn.Open();
                var tx = sqlconn.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    var count = 0;
                    foreach (var strsql in sqlStringList)
                    {
                        if (strsql.Trim().Length <= 1) continue;
                        cmd.CommandText = strsql;
                        count += cmd.ExecuteNonQuery();
                    }
                    tx.Commit();
                    return count;
                }
                catch
                {
                    tx.Rollback();
                    return 0;
                }
            }
        }

        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="sqlStringList">SQL语句的哈希表（key为sql语句，value是该语句的VerticaParameter[]）</param>
        public static void ExecuteSqlTran(Hashtable sqlStringList)
        {
            using (var sqlconn = new VerticaConnection(ConnectionString))
            {
                sqlconn.Open();
                using (var trans = sqlconn.BeginTransaction())
                {
                    var cmd = new VerticaCommand();
                    try
                    {
                        foreach (DictionaryEntry myDe in sqlStringList)
                        {
                            var cmdText = myDe.Key.ToString();
                            var cmdParms = (VerticaParameter[])myDe.Value;
                            PrepareCommand(cmd, sqlconn, trans, cmdText, cmdParms);
                            var val = cmd.ExecuteNonQuery();
                            cmd.Parameters.Clear();
                        }
                        trans.Commit();
                    }
                    catch
                    {
                        trans.Rollback();
                        throw;
                    }
                }
            }
        }

        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="cmdList">SQL语句的哈希表（key为sql语句，value是该语句的VerticaParameter[]）</param>
        public int ExecuteSqlTran(List<CommandInfo> cmdList)
        {
            using (var sqlconn = new VerticaConnection(ConnectionString))
            {
                sqlconn.Open();
                using (var trans = sqlconn.BeginTransaction())
                {
                    var cmd = new VerticaCommand();
                    try
                    {
                        var count = 0;
                        foreach (var myDe in cmdList)
                        {
                            var cmdText = myDe.CommandText;
                            var cmdParms = myDe.Parameters;
                            PrepareCommand(cmd, sqlconn, trans, cmdText, cmdParms);

                            if (myDe.EffentNextType == EffentNextType.WhenHaveContine ||
                                myDe.EffentNextType == EffentNextType.WhenNoHaveContine)
                            {
                                if (myDe.CommandText.ToLower().IndexOf("count(", StringComparison.Ordinal) == -1)
                                {
                                    trans.Rollback();
                                    return 0;
                                }
                                var obj = cmd.ExecuteScalar();
                                bool isHave;
                                if (obj == null && obj == DBNull.Value)
                                {
                                    isHave = false;
                                }
                                isHave = Convert.ToInt32(obj) > 0;

                                if (myDe.EffentNextType == EffentNextType.WhenHaveContine && !isHave)
                                {
                                    trans.Rollback();
                                    return 0;
                                }
                                if (myDe.EffentNextType == EffentNextType.WhenNoHaveContine && isHave)
                                {
                                    trans.Rollback();
                                    return 0;
                                }
                                continue;
                            }
                            var val = cmd.ExecuteNonQuery();
                            count += val;
                            if (myDe.EffentNextType == EffentNextType.ExcuteEffectRows && val == 0)
                            {
                                trans.Rollback();
                                return 0;
                            }
                            cmd.Parameters.Clear();
                        }
                        trans.Commit();
                        return count;
                    }
                    catch (Exception ex)
                    {
                        trans.Rollback();
                        throw;
                    }
                }
            }
        }

        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="cmdList">SQL语句的哈希表（key为sql语句，value是该语句的VerticaParameter[]）</param>
        public int ExecuteSqlTran(List<CommandInfo> cmdList, DataTable dt, string strTableName)
        {
            using (var sqlconn = new VerticaConnection(ConnectionString))
            {
                sqlconn.Open();
                using (var trans = sqlconn.BeginTransaction())
                {
                    var cmd = new VerticaCommand();
                    try
                    {
                        var count = 0;
                        foreach (var myDe in cmdList)
                        {
                            var cmdText = myDe.CommandText;
                            var cmdParms = myDe.Parameters;
                            PrepareCommand(cmd, sqlconn, trans, cmdText, cmdParms);

                            if (myDe.EffentNextType == EffentNextType.WhenHaveContine ||
                                myDe.EffentNextType == EffentNextType.WhenNoHaveContine)
                            {
                                if (myDe.CommandText.ToLower().IndexOf("count(", StringComparison.Ordinal) == -1)
                                {
                                    trans.Rollback();
                                    return 0;
                                }
                                var obj = cmd.ExecuteScalar();
                                bool isHave;
                                if (obj == null && obj == DBNull.Value)
                                {
                                    isHave = false;
                                }
                                isHave = Convert.ToInt32(obj) > 0;

                                if (myDe.EffentNextType == EffentNextType.WhenHaveContine && !isHave)
                                {
                                    trans.Rollback();
                                    return 0;
                                }
                                if (myDe.EffentNextType == EffentNextType.WhenNoHaveContine && isHave)
                                {
                                    trans.Rollback();
                                    return 0;
                                }
                                continue;
                            }
                            var val = cmd.ExecuteNonQuery();
                            count += val;
                            if (myDe.EffentNextType == EffentNextType.ExcuteEffectRows && val == 0)
                            {
                                trans.Rollback();
                                return 0;
                            }
                            cmd.Parameters.Clear();
                        }
                        BulkCopy(sqlconn, dt, strTableName);
                        trans.Commit();
                        return count;
                    }
                    catch
                    {
                        trans.Rollback();
                        throw;
                    }
                }
            }
        }

        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="cmdList">SQL语句的哈希表（key为sql语句，value是该语句的VerticaParameter[]）</param>
        public int ExecuteSqlTran(List<CommandInfo> cmdList, DataTable dt, string strTableName, int limit)
        {
            using (var sqlconn = new VerticaConnection(ConnectionString))
            {
                sqlconn.Open();
                using (var trans = sqlconn.BeginTransaction())
                {
                    var cmd = new VerticaCommand();
                    try
                    {
                        var count = 0;
                        List<CommandInfo> limitList = new List<CommandInfo>();
                        int index = 0;
                        int temp = 0;
                        StringBuilder sb = new StringBuilder();
                        foreach (var myDe in cmdList)
                        {
                            temp++;
                            //如果索引小于限制数
                            if (index < limit)
                            {
                                sb.Append($"{myDe.CommandText};");
                                //处理当数据小于等于限制数时
                                if (index == cmdList.Count - 1)
                                {
                                    limitList.Add(
                                            new CommandInfo()
                                            {
                                                CommandText = sb.ToString()
                                            }
                                        );
                                }
                            }
                            //达到限制数,拼接成一条语句
                            else
                            {
                                limitList.Add(
                                            new CommandInfo()
                                            {
                                                CommandText = sb.ToString()
                                            }
                                );
                                sb.Clear();
                                sb.Append($"{myDe.CommandText};");
                                index = 0;
                            }
                            index++;
                        }
                        //获取最后没有添加到list中的sql语句
                        if (sb.Length > 0)
                        {
                            limitList.Add(
                                            new CommandInfo()
                                            {
                                                CommandText = sb.ToString()
                                            }
                                );
                        }
                        foreach (var myDe in limitList)
                        {
                            var cmdText = myDe.CommandText;
                            var cmdParms = myDe.Parameters;
                            PrepareCommand(cmd, sqlconn, trans, cmdText, cmdParms);

                            if (myDe.EffentNextType == EffentNextType.WhenHaveContine ||
                                myDe.EffentNextType == EffentNextType.WhenNoHaveContine)
                            {
                                if (myDe.CommandText.ToLower().IndexOf("count(", StringComparison.Ordinal) == -1)
                                {
                                    trans.Rollback();
                                    return 0;
                                }
                                var obj = cmd.ExecuteScalar();
                                bool isHave;
                                if (obj == null && obj == DBNull.Value)
                                {
                                    isHave = false;
                                }
                                isHave = Convert.ToInt32(obj) > 0;

                                if (myDe.EffentNextType == EffentNextType.WhenHaveContine && !isHave)
                                {
                                    trans.Rollback();
                                    return 0;
                                }
                                if (myDe.EffentNextType == EffentNextType.WhenNoHaveContine && isHave)
                                {
                                    trans.Rollback();
                                    return 0;
                                }
                                continue;
                            }
                            var val = cmd.ExecuteNonQuery();
                            count += val;
                            if (myDe.EffentNextType == EffentNextType.ExcuteEffectRows && val == 0)
                            {
                                trans.Rollback();
                                return 0;
                            }
                            cmd.Parameters.Clear();
                        }
                        BulkCopy(sqlconn, dt, strTableName);
                        trans.Commit();
                        return count;
                    }
                    catch
                    {
                        trans.Rollback();
                        throw;
                    }
                }
            }
        }

        /// <summary>
        /// 执行单条语句，实现数据库事务。
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="strTableName">需要删除并插入数据的表名</param>
        /// <param name="delSqlWhere"></param>
        /// <returns></returns>
        /// Writer：梁家健
        /// Create Date：2017-07-13
        public int ExecuteSqlTran(DataTable dt, string strTableName, string sql)
        {
            using (var sqlconn = new VerticaConnection(ConnectionString))
            {
                sqlconn.Open();
                using (var trans = sqlconn.BeginTransaction())
                {
                    try
                    {
                        var cmd = new VerticaCommand();
                        PrepareCommand(cmd, sqlconn, trans, sql, null);
                        var count = cmd.ExecuteNonQuery();
                        BulkCopy(sqlconn, dt, strTableName);
                        trans.Commit();
                        return count;
                    }
                    catch
                    {
                        trans.Rollback();
                        throw;
                    }
                }
            }
        }

        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="sqlStringList">SQL语句的哈希表（key为sql语句，value是该语句的VerticaParameter[]）</param>
        public static void ExecuteSqlTranWithIndentity(List<CommandInfo> sqlStringList)
        {
            using (var sqlconn = new VerticaConnection(ConnectionString))
            {
                sqlconn.Open();
                using (var trans = sqlconn.BeginTransaction())
                {
                    var cmd = new VerticaCommand();
                    try
                    {
                        var indentity = 0;
                        foreach (var myDe in sqlStringList)
                        {
                            var cmdText = myDe.CommandText;
                            var cmdParms = myDe.Parameters;
                            foreach (var q in cmdParms)
                            {
                                if (q.Direction == ParameterDirection.InputOutput)
                                {
                                    q.Value = indentity;
                                }
                            }
                            PrepareCommand(cmd, sqlconn, trans, cmdText, cmdParms);
                            var val = cmd.ExecuteNonQuery();
                            foreach (var q in cmdParms)
                            {
                                if (q.Direction == ParameterDirection.Output)
                                {
                                    indentity = Convert.ToInt32(q.Value);
                                }
                            }
                            cmd.Parameters.Clear();
                        }
                        trans.Commit();
                    }
                    catch
                    {
                        trans.Rollback();
                        throw;
                    }
                }
            }
        }

        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="sqlStringList">SQL语句的哈希表（key为sql语句，value是该语句的VerticaParameter[]）</param>
        public static void ExecuteSqlTranWithIndentity(Hashtable sqlStringList)
        {
            using (var sqlconn = new VerticaConnection(ConnectionString))
            {
                sqlconn.Open();
                using (var trans = sqlconn.BeginTransaction())
                {
                    var cmd = new VerticaCommand();
                    try
                    {
                        var indentity = 0;
                        foreach (DictionaryEntry myDe in sqlStringList)
                        {
                            var cmdText = myDe.Key.ToString();
                            var cmdParms = (VerticaParameter[])myDe.Value;
                            foreach (var q in cmdParms)
                            {
                                if (q.Direction == ParameterDirection.InputOutput)
                                {
                                    q.Value = indentity;
                                }
                            }
                            PrepareCommand(cmd, sqlconn, trans, cmdText, cmdParms);
                            var val = cmd.ExecuteNonQuery();
                            foreach (var q in cmdParms)
                            {
                                if (q.Direction == ParameterDirection.Output)
                                {
                                    indentity = Convert.ToInt32(q.Value);
                                }
                            }
                            cmd.Parameters.Clear();
                        }
                        trans.Commit();
                    }
                    catch
                    {
                        trans.Rollback();
                        throw;
                    }
                }
            }
        }

        /// <summary>
        /// 添加参数
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="conn"></param>
        /// <param name="trans"></param>
        /// <param name="cmdText"></param>
        /// <param name="cmdParms"></param>
        private static void PrepareCommand(VerticaCommand cmd, VerticaConnection conn, VerticaTransaction trans, string cmdText,
            VerticaParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            if (trans != null)
                cmd.Transaction = trans;
            cmd.CommandType = CommandType.Text;//cmdType;//设置解释sql语句的类型为“文本”类型（也是就说该函数不适用于存储过程）  
            if (cmdParms == null) return;
            foreach (var parameter in cmdParms)
            {
                if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                    (parameter.Value == null))
                {
                    parameter.Value = DBNull.Value;
                }
                cmd.Parameters.Add(parameter);
            }
        }

        #endregion

        #region Merge
        /// <summary>
        /// 枚举
        /// </summary>
        /// <param name="TempTableName"></param>
        /// <param name="TargetTableName"></param>
        /// <param name="OnConditions"></param>
        /// <param name="SetConditions"></param>
        /// <param name="InsertConditions"></param>
        /// <param name="ValuesConditions"></param>
        /// Writer：梁家健
        /// Create Date：2017-05-15
        public void Merge(string TempTableName, string TargetTableName, DataTable dt, string OnConditions, string SetConditions,
            string InsertConditions, string ValuesConditions, bool IsInsert)
        {
            try
            {
                DropTempTable(TempTableName);
                CreateTempTable(TempTableName, TargetTableName);
                BulkCopy(dt, TempTableName);
                MergeIntoTable(TempTableName, TargetTableName, OnConditions, SetConditions, InsertConditions,
                ValuesConditions, IsInsert);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                DropTempTable(TempTableName);
            }
        }

        /// <summary>
        /// 删除临时表
        /// </summary>
        /// <param name="TableName"></param>
        /// Writer：梁家健
        /// Create Date：2017-05-15
        public void DropTempTable(string TempTableName)
        {
            try
            {
                var sql = $@"drop table if exists {TempTableName}";
                var cmdList = new List<CommandInfo>
                {
                    new CommandInfo
                    {
                        CommandText = sql,
                    }
                };
                ExecuteSqlTran(cmdList);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// <summary>
        /// 根据源表创建临时表
        /// </summary>
        /// <param name="TempTableName"></param>
        /// <param name="SourceTableName"></param>
        /// Writer：梁家健
        /// Create Date：2017-05-15
        public void CreateTempTable(string TempTableName, string SourceTableName)
        {
            try
            {
                var sql = $@"SELECT  *
INTO    {TempTableName}
FROM    {SourceTableName} limit 0 ";
                var cmdList = new List<CommandInfo>
                {
                    new CommandInfo
                    {
                        CommandText = sql,
                    }
                };
                ExecuteSqlTran(cmdList);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// <summary>
        /// 枚举插入
        /// </summary>
        /// <param name="TempTableName"></param>
        /// <param name="TargetTableName"></param>
        /// <param name="OnConditions"></param>
        /// <param name="SetConditions"></param>
        /// <param name="InsertConditions"></param>
        /// <param name="ValuesConditions"></param>
        /// Writer：梁家健
        /// Create Date：2017-05-15
        public void MergeIntoTable(string TempTableName, string TargetTableName, string OnConditions, string SetConditions,
            string InsertConditions, string ValuesConditions, bool IsInsert)
        {
            try
            {
                var sql = $@"MERGE INTO {TargetTableName} targetTable
    USING {TempTableName} sourceTable
    ON ( 
          {OnConditions}
       )
    WHEN MATCHED 
        THEN UPDATE
          SET   {SetConditions}
    {(IsInsert ? $@"WHEN NOT MATCHED 
        THEN INSERT (
                       {InsertConditions}
                    )
          VALUES    ( 
                       {ValuesConditions}
                    )" : "")}";
                var cmdList = new List<CommandInfo>
                {
                    new CommandInfo
                    {
                        CommandText = sql,
                    }
                };
                ExecuteSqlTran(cmdList);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }
        #endregion

        #region Judge
        /// <summary>
        /// 判断表是否存在
        /// </summary>
        /// <param name="SchemaName"></param>
        /// <param name="TempTableName"></param>
        /// <returns></returns>
        public bool JudgeTable(string SchemaName, string TableName)
        {
            try
            {
                var sql =
                    $@"select count(1) from tables where table_schema='{SchemaName}' and table_name='{TableName}'";
                var cmd = GetSqlStringCommand(sql);
                var count = Convert.ToInt32(ExecuteScalar(cmd));
                return count != 0;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }
        /// <summary>
        /// 判断视图是否存在
        /// </summary>
        /// <param name="SchemaName"></param>
        /// <param name="TempTableName"></param>
        /// <returns></returns>
        public bool JudgeView(string SchemaName, string TableName)
        {
            try
            {
                var sql =
                    $@"select count(1) from views where table_schema='{SchemaName}' and table_name='{TableName}'";
                var cmd = GetSqlStringCommand(sql);
                var count = Convert.ToInt32(ExecuteScalar(cmd));
                return count != 0;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        #endregion

        /*********************示例****************************************
        1)直接执行sql语句

                DbHelper db = new DbHelper();
                DbCommand cmd = db.GetSqlStringCommond("insert t1 (id)values(‘haha‘)");
                db.ExecuteNonQuery(cmd);
        2)执行存储过程

                DbHelper db = new DbHelper();
                DbCommand cmd = db.GetStoredProcCommond("t1_insert");
                db.AddInParameter(cmd, "@id", DbType.String, "heihei");
                db.ExecuteNonQuery(cmd);
        3)返回DataSet

                DbHelper db = new DbHelper();
                DbCommand cmd = db.GetSqlStringCommond("select * from t1");
                DataSet ds = db.ExecuteDataSet(cmd);
        4)返回DataTable

                DbHelper db = new DbHelper();
                DbCommand cmd = db.GetSqlStringCommond("t1_findall");
                DataTable dt = db.ExecuteDataTable(cmd);
        5)输入参数/输出参数/返回值的使用(比较重要哦)

                DbHelper db = new DbHelper();
                DbCommand cmd = db.GetStoredProcCommond("t2_insert");
                db.AddInParameter(cmd, "@timeticks", DbType.Int64, DateTime.Now.Ticks);
                db.AddOutParameter(cmd, "@outString", DbType.String, 20);
                db.AddReturnParameter(cmd, "@returnValue", DbType.Int32);

                db.ExecuteNonQuery(cmd);

                string s = db.GetParameter(cmd, "@outString").Value as string;//out parameter
                int r = Convert.ToInt32(db.GetParameter(cmd, "@returnValue").Value);//return value

        6)DataReader使用

              DbHelper db = new DbHelper();
                DbCommand cmd = db.GetStoredProcCommond("t2_insert");
                db.AddInParameter(cmd, "@timeticks", DbType.Int64, DateTime.Now.Ticks);
                db.AddOutParameter(cmd, "@outString", DbType.String, 20);
                db.AddReturnParameter(cmd, "@returnValue", DbType.Int32);

                using (DbDataReader reader = db.ExecuteReader(cmd))
                {
                    dt.Load(reader);
                }        
                string s = db.GetParameter(cmd, "@outString").Value as string;//out parameter
                int r = Convert.ToInt32(db.GetParameter(cmd, "@returnValue").Value);//return value


        7)事务的使用.(项目中需要将基本的数据库操作组合成一个完整的业务流时,代码级的事务是必不可少的哦)

        以上我们好像没有指定数据库连接字符串,大家如果看下DbHelper的代码,就知道要使用它必须在config中配置两个参数,如下:

            pubic void DoBusiness()
            {
                using (Trans t = new Trans())
                {
                    try
                    {
                        D1(t);
                        throw new Exception();//如果有异常,会回滚滴
                        D2(t);
                        t.Commit();
                    }
                    catch
                    {
                        t.RollBack();
                    }
                }
            }
            public void D1(Trans t)
            {
                DbHelper db = new DbHelper();
                DbCommand cmd = db.GetStoredProcCommond("t2_insert");
                db.AddInParameter(cmd, "@timeticks", DbType.Int64, DateTime.Now.Ticks);
                db.AddOutParameter(cmd, "@outString", DbType.String, 20);
                db.AddReturnParameter(cmd, "@returnValue", DbType.Int32);

                if (t == null) db.ExecuteNonQuery(cmd);
                else db.ExecuteNonQuery(cmd,t);

                string s = db.GetParameter(cmd, "@outString").Value as string;//out parameter
                int r = Convert.ToInt32(db.GetParameter(cmd, "@returnValue").Value);//return value
            }
            public void D2(Trans t)
            {
                DbHelper db = new DbHelper();
                DbCommand cmd = db.GetSqlStringCommond("insert t1 (id)values(‘..‘)");        
                if (t == null) db.ExecuteNonQuery(cmd);
                else db.ExecuteNonQuery(cmd, t);
            }
            <appSettings>
                <add key="DbHelperProvider" value="System.Data.SqlClient"/>
                <add key="DbHelperConnectionString" value="Data Source=(local);Initial Catalog=DbHelperTest;Persist Security Info=True;User ID=sa;Password=sa"/>
            appSettings>* */


    }
}
